"""
Case Type   : Ubtree索引增强
Case Name   : 测试ubtree恢复_范围分区表_切割分区后回滚
Create At   : 2025-11-14
Owner       : sungang14
Description :
    1、创建
    2、创建pcr索引
    3、查看索引类型
    4、插入测试数据
    5、查询表、索引大小
    6、事务操作（切割分区+回滚）
    7、查询表、索引大小
    8、清理环境
Expect      :
    1、创建成功
    2、成功
    3、ustore pcr索引
    4、插入1000条成功
    5、成功
    6、分割成功，回滚成功
    7、同步骤5结果
    8、清理环境成功
History     :
"""

import os
import time
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.userNode = Node('PrimaryDbUser')
        self.table_name = f"t_ustore_ubtree_0083"
        self.index_name = f"i_ustore_ubtree_0083"
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_01 = f"""create table {self.table_name} (id int, dt date) with (storage_type=ustore)
                                    partition by range (dt) (
                                        partition p1 values less than ('2023-01-01'),
                                        partition p2 values less than ('2023-02-01')
                                    );"""
        self.prepare_data_02 = f"create index {self.index_name} on {self.table_name} using ubtree(dt) with (index_type=pcr);"
        self.prepare_data_03 = f"select relkind, reloptions from pg_class where relname like '{self.index_name}%';"
        self.prepare_data_04 = f"insert into {self.table_name} select g, case when g%2=0 then '2023-01-15'::date else '2022-12-15'::date end from generate_series(1,1000) as g;"
        self.prepare_data_05_01 = f"select count(*) from {self.table_name};"
        self.prepare_data_05_02 = f"select checksum({self.table_name}::text) from {self.table_name};"
        self.prepare_data_05_03 = f"select pg_size_pretty(pg_relation_size('{self.index_name}')) as init_index_size;"
        self.prepare_data_06 = f"begin;" \
                               f"alter table {self.table_name} split partition p2 at ('2023-01-15') into (partition p2_1, partition p2_2);" \
                               f"rollback;"

    def test_slowsql(self):
        text = '--step1:创建范围分区表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = '--step2:创建pcr索引;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:查询索引类型;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn("index_type=pcr,storage_type=ustore", data_cmd, '执行失败' + text)

        text = '--step4:插入测试数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(f"INSERT 0 1000", data_cmd, '执行失败' + text)

        text = '--step5:查询表、索引大小;expect:成功--'
        logger.info(text)
        data_cmd_01 = self.comsh.execut_db_sql(self.prepare_data_05_01)
        logger.info(data_cmd_01)
        self.assertEqual(f"1000", data_cmd_01.splitlines()[-2].strip(), '执行失败' + text)
        data_cmd_02 = self.comsh.execut_db_sql(self.prepare_data_05_02)
        logger.info(data_cmd_02)
        self.assertNotEqual(f"0", data_cmd_02.splitlines()[-2].strip(), '执行失败' + text)
        data_cmd_03 = self.comsh.execut_db_sql(self.prepare_data_05_03)
        logger.info(data_cmd_03)
        self.assertEqual(f"48 kB", data_cmd_03.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step6:事务操作（分割+回滚）;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertIn(self.constant.ROLLBACK_MSG, data_cmd, '执行失败' + text)

        text = '--step7:查询表、索引大小;expect:成功--'
        logger.info(text)
        data_cmd_04 = self.comsh.execut_db_sql(self.prepare_data_05_01)
        logger.info(data_cmd_04)
        self.assertEqual(data_cmd_01, data_cmd_04, '执行失败' + text)
        data_cmd_05 = self.comsh.execut_db_sql(self.prepare_data_05_02)
        logger.info(data_cmd_05)
        self.assertEqual(data_cmd_02, data_cmd_05, '执行失败' + text)
        data_cmd_06 = self.comsh.execut_db_sql(self.prepare_data_05_03)
        logger.info(data_cmd_06)
        self.assertEqual(data_cmd_03, data_cmd_06, '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
